﻿/**
@file
    TableFiles.cs
@brief
    Copyright 2009 Creative Crew. All rights reserved.
@author
    William Chang
@version
    0.1
@date
    - Created: 2008-09-05
    - Modified: 2009-04-21
    .
@note
    References:
    - General:
        - http://www.odetocode.com/Articles/163.aspx
        - http://www.uploadify.com/forum/viewtopic.php?f=5&t=45
        - http://www.willasrari.com/blog/irequiressessionstate-and-dynamic-data-in-static-pages/000262.aspx
        - http://weblogs.asp.net/jgalloway/archive/2008/01/08/large-file-uploads-in-asp-net.aspx
        .
    - File:
        - http://www.dotnetcurry.com/ShowArticle.aspx?ID=129
        - http://www.codeproject.com/KB/aspnet/VideoUploaderControl.aspx?display=Print
        .
    .
*/

using System;
using System.Data;
using System.Collections.Generic;
using System.Web;

namespace ent {

/// <summary>Class TableFiles extends TableCommon</summary>
public class TableFiles : TableCommon {

#region Fields

    public const String TBL__files = "files";
    public const String TBL__files___PK__file_id = "file_id";
    public const String TBL__files__file_id = "file_id";
    public const String TBL__files__file_type = "file_type";
    public const String TBL__files__file_uploader_user_id = "file_uploader_user_id";
    public const String TBL__files__file_name = "file_name";
    public const String TBL__files__file_name_slug = "file_name_slug";
    public const String TBL__files__file_description = "file_description";
    public const String TBL__files__file_binary = "file_binary";
    public const String TBL__files__file_extension = "file_extension";
    public const String TBL__files__file_size_bytes = "file_size_bytes";
    public const String TBL__files__file_count_download = "file_count_download";
    public const String TBL__files__file_date_created = "file_date_created";
    public const String TBL__files__file_deleted = "file_deleted";

    public const String TBL__file_filter_extensions = "file_filter_extensions";
    public const String TBL__file_filter_extensions___PK__extension_id = "extension_id";
    public const String TBL__file_filter_extensions__extension_id = "extension_id";
    public const String TBL__file_filter_extensions__extension_name = "extension_name";
    public const String TBL__file_filter_extensions__extension_description = "extension_description";
    public const String TBL__file_filter_extensions__extension_file_type = "extension_file_type";
    public const String TBL__file_filter_extensions__extension_deleted = "extension_deleted";

#endregion    
    
    /// <summary>Default constructor.</summary>
    public TableFiles() {}
    /// <summary>Get relevant columns for user.</summary>
    public static System.Collections.SortedList getColumnsRelevantFiles() {
        System.Collections.SortedList p = new System.Collections.SortedList();

        p.Add(1, TBL__files__file_id);
        p.Add(2, TBL__files__file_type);
        p.Add(3, TBL__files__file_uploader_user_id);
        p.Add(4, TBL__files__file_name);
        p.Add(5, TBL__files__file_extension);
        p.Add(6, TBL__files__file_size_bytes);
        p.Add(7, TBL__files__file_count_download);
        p.Add(8, TBL__files__file_date_created);
        p.Add(9, TBL__files__file_deleted);
        return p;
    }
    /// <summary>Get relevant columns for user.</summary>
    public static System.Collections.SortedList getColumnsRelevantFileFilterExtensions() {
        System.Collections.SortedList p = new System.Collections.SortedList();

        p.Add(1, TBL__file_filter_extensions__extension_id);
        p.Add(2, TBL__file_filter_extensions__extension_file_type);
        p.Add(3, TBL__file_filter_extensions__extension_name);
        p.Add(4, TBL__file_filter_extensions__extension_deleted);
        return p;
    }

#region Stored Procedures: Files

    /// <summary>Create (INSERT) database item using stored procedure.</summary>
    public int createFile(System.Collections.Hashtable parameters) {
        return (int)runStoredProcedure("spInsertFile", parameters, DatabaseCommon.enumSqlDataReturn.Integer);
    }
    /// <summary>
    /// Get (SELECT) database item using stored procedure.
    /// 
    /// <para>parameter @limit Limit the total of records.</para>
    /// </summary>
    /// <remarks>Stored Procedure using Dynamic SQL</remarks>
    public DataTable getContentsRecent(System.Collections.Hashtable parameters) {
        return (DataTable)runStoredProcedure("spSelectFilesRecent", parameters, DatabaseCommon.enumSqlDataReturn.DataTable);
    }
    /// <summary>Set (UPDATE) database item using stored procedure.</summary>
    public bool setFile(System.Collections.Hashtable parameters) {
        return (bool)runStoredProcedure("spUpdateFile", parameters, DatabaseCommon.enumSqlDataReturn.Boolean);
    }

#endregion

#region Dynamic SQL

    /// <summary>Get file by id.</summary>
    public DataRow getFile(int id) {
        return base.dynamicSqlSelect(null, TableFiles.TBL__files, TableFiles.TBL__files___PK__file_id + " = " + id).Rows[0];
    }
    /// <summary>Get file filter extension by id.</summary>
    public DataRow getFileFilterExtension(int id) {
        return base.dynamicSqlSelect(null, TableFiles.TBL__file_filter_extensions, TableFiles.TBL__file_filter_extensions___PK__extension_id + " = " + id).Rows[0];
    }
    /// <summary>Get files.</summary>
    public DataTable getFiles(System.Collections.IDictionary parameters) {
        return base.dynamicSqlSelect(parameters, TableFiles.TBL__files, TableFiles.TBL__files__file_deleted + " = 0");
    }
    /// <summary>Get file filter extensions.</summary>
    public DataTable getFileFilterExtensions(System.Collections.IDictionary parameters) {
        return base.dynamicSqlSelect(parameters, TableFiles.TBL__file_filter_extensions, TableFiles.TBL__file_filter_extensions__extension_deleted + " = 0");
    }
    /// <summary>Get trashed files.</summary>
    public DataTable getFilesTrashed(System.Collections.IDictionary parameters) {
        return base.dynamicSqlSelect(parameters, TableFiles.TBL__files, TBL__files__file_deleted + " = 1");
    }
    /// <summary>Get trashed file filter extensions.</summary>
    public DataTable getFileFilterExtensionsTrashed(System.Collections.IDictionary parameters) {
        return base.dynamicSqlSelect(parameters, TableFiles.TBL__file_filter_extensions, TableFiles.TBL__file_filter_extensions__extension_deleted + " = 1");
    }
    /// <summary>Remove file.</summary>
    public bool removeFile(int id) {
        return base.dynamicSqlDelete(TableFiles.TBL__files, TableFiles.TBL__files___PK__file_id + " = " + id);
    }
    /// <summary>Remove file filter extension.</summary>
    public bool removeFileFilterExtension(int id) {
        return base.dynamicSqlDelete(TableFiles.TBL__file_filter_extensions, TableFiles.TBL__file_filter_extensions___PK__extension_id + " = " + id);
    }
    /// <summary>Restore trashed file.</summary>
    public bool restoreFile(int id) {
        System.Collections.Hashtable p1 = new System.Collections.Hashtable();
        p1.Add(TableFiles.TBL__files__file_deleted, 0);
        return base.dynamicSqlUpdate(p1, TableFiles.TBL__files, TableFiles.TBL__files___PK__file_id + " = " + id);
    }
    /// <summary>Restore trashed file filter extension.</summary>
    public bool restoreFileFilterExtension(int id) {
        System.Collections.Hashtable p1 = new System.Collections.Hashtable();
        p1.Add(TableFiles.TBL__file_filter_extensions__extension_deleted, 0);
        return base.dynamicSqlUpdate(p1, TableFiles.TBL__file_filter_extensions, TableFiles.TBL__file_filter_extensions___PK__extension_id + " = " + id);
    }
    /// <summary>Trash file.</summary>
    public bool trashFile(int id) {
        System.Collections.Hashtable p1 = new System.Collections.Hashtable();
        p1.Add(TableFiles.TBL__files__file_deleted, 1);
        return base.dynamicSqlUpdate(p1, TableFiles.TBL__files, TableFiles.TBL__files___PK__file_id + " = " + id);
    }
    /// <summary>Trash file filter extension.</summary>
    public bool trashFileFilterExtension(int id) {
        System.Collections.Hashtable p1 = new System.Collections.Hashtable();
        p1.Add(TableFiles.TBL__file_filter_extensions__extension_deleted, 1);
        return base.dynamicSqlUpdate(p1, TableFiles.TBL__file_filter_extensions, TableFiles.TBL__file_filter_extensions___PK__extension_id + " = " + id);
    }

#endregion

    /// <summary>To slug (filename and URL friendly).</summary>
    /// <remarks>http://www.intrepidstudios.com/blog/2009/2/10/function-to-generate-a-url-friendly-string.aspx</remarks>
    public static String toSlug(string s) {
        s = System.Text.RegularExpressions.Regex.Replace(s, @"[^a-zA-Z0-9\s-_]", ""); // Invalid characters.        
        s = System.Text.RegularExpressions.Regex.Replace(s, @"\s+", " ").Trim(); // Convert multiple spaces into one space.
        s = s.Substring(0, s.Length <= 1024 ? s.Length : 1024).Trim(); // Cut and trim.
        s = System.Text.RegularExpressions.Regex.Replace(s, @"\s", "_"); // Convert each single space to a underscore.
        return s;
    }

}

} // END namespace ent
